﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Reports.TurnoverBook
{
    public class DataLoader
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <param name="ConnectionString"></param>
        /// <param name="DepName"></param>
        /// <param name="DateF"></param>
        /// <param name="DateT"></param>
        public static void GetData(Reports.TurnoverBook.DataSet.DataDataTable data, string ConnectionString, string DepList, DateTime DateF, DateTime DateT)
        {
            /*
             isnull(CD.InputLines.SupplierPrice,dbo.GetSupplierPrice(CD.Parties.Id)) as OptPrice,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateF
) as StartRest,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate BETWEEN @DateF AND @DateT
AND g.Qntty > 0
) as IncomeCount,
(
select isnull(sum(g.Qntty),0) * -1
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate >= @DateF AND d.docDate < @DateT
AND g.Qntty < 0
) as ExpenceCount,
(
select isnull(sum(g.Qntty),0)
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate < @DateT
) as EndRest,
(
select isnull(sum(g.Qntty),0) * -1
from CA.GoodsDocLines g inner join CA.Documents d
on d.Id = g.DocId
where g.PartyId = CD.Parties.Id and d.docDate >= @DateF AND d.docDate < @DateT
AND g.Qntty < 0 AND CD.Parties.DepartmentId = d.DepartmentId
and d.DocTypeId = 3
) as CashExpenceCount
             */
            string QueryString =
                @"SELECT DISTINCT ISNULL(CO.Departments.Name,'') as DepartName, Dtl.FullName as GoodsName,
vwInputLines.RetailPrice as RoznPrice, vwInputLines.SupplierPrice as OptPrice, 
(SELECT  ISNULL(SUM(vwgi.Qntty),0) AS sum
FROM         dbo.vwGoodsFlow vwgi
WHERE     (vwgi.DocDate < @DateF) AND (vwgi.GoodsId = Dtl.Id) and (vwgi.PartyId=vwGoodsFlow.PartyId)) as StartRest,
(SELECT ISNULL(sum(f.Qntty),0)
FROM         vwGoodsFlow f 
where (f.DocDate BETWEEN @DateF AND @DateT)
AND f.GoodsId = Dtl.Id AND f.Qntty >0 and (f.PartyId=vwGoodsFlow.PartyId))  as IncomeCount,
(SELECT ISNULL(sum(f.Qntty),0) * -1
FROM         vwGoodsFlow f
where (f.DocDate BETWEEN @DateF AND @DateT)
AND f.GoodsId = Dtl.Id AND f.Qntty < 0 and (f.PartyId=vwGoodsFlow.PartyId)) as ExpenceCount,
(SELECT  ISNULL(SUM(vwgi.Qntty),0) AS sum
FROM         dbo.vwGoodsFlow vwgi
WHERE     (vwgi.DocDate < @DateT ) AND (vwgi.GoodsId = Dtl.Id) and (vwgi.PartyId=vwGoodsFlow.PartyId)) as EndRest
FROM         vwGoodsFlow INNER JOIN
vwGoodsDetailed Dtl ON vwGoodsFlow.GoodsId = Dtl.Id INNER JOIN
vwInputLines ON vwGoodsFlow.PartyId = vwInputLines.PartyId INNER JOIN 
CA.Documents ON CA.Documents.Id = vwGoodsFlow.DocId LEFT OUTER JOIN
CO.Departments ON CA.Documents.DepartmentId = CO.Departments.Id
where vwGoodsFlow.DocDate BETWEEN @DateF AND @DateT";
            string QueryString1 = @"DECLARE @tab TABLE(pId int primary key clustered)
DECLARE @t TABLE(pId int primary key clustered)

INSERT INTO @t
SELECT  L.PartyId
FROM  CA.GoodsDocLines AS L  INNER JOIN
CA.Documents AS D  ON L.DocId = D.Id 
WHERE D.DocDate < @DateF
GROUP BY L.PartyId 
HAVING SUM(L.Qntty) > 0

insert into @tab
SELECT DISTINCT  CA.GoodsDocLines.PartyId
FROM CA.GoodsDocLines INNER JOIN
CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE     (CA.Documents.DocDate BETWEEN @DateF AND @DateT)
OR CA.GoodsDocLines.PartyId IN (SELECT pId from @t)

SELECT  ISNULL(CO.Departments.Name,'Департамент') as DepartName, 
CD.Parties.GoodsId as GoodsCode,
ISNULL(vwGoodsDetailed.FullName, ' Неизвестный') AS GoodsName, 
CD.Parties.RetailPrice as RoznPrice,
(select isnull(sum(Qntty),0) from dbo.vwGoodsTransfer where dbo.vwGoodsTransfer.PartyId = CD.Parties.Id
and dbo.vwGoodsTransfer.DocDate < @DateF) 
as StartRest,

(select isnull(sum(Qntty),0) from dbo.vwGoodsTransfer where 
dbo.vwGoodsTransfer.PartyId = CD.Parties.Id and 
dbo.vwGoodsTransfer.DocDate BETWEEN @DateF AND @DateT and Qntty>1) 
as IncomeCount,

(select isnull(sum(Qntty),0) *-1 from dbo.vwGoodsTransfer as gt
where gt.PartyId = CD.Parties.Id and gt.docDate >= @DateF and gt.docDate < @DateT
and Qntty < 0
) as ExpenceCount,

(select isnull(sum(Qntty),0) from dbo.vwGoodsTransfer as gt
where gt.PartyId = CD.Parties.Id  and gt.docDate < @DateT
) as EndRest,

(select isnull(sum(Qntty),0) * -1 from dbo.vwGoodsTransfer as gt
where gt.PartyId = CD.Parties.Id and CD.Parties.DepartmentId = gt.DepartmentId
and gt.DocTypeId = 3 and gt.docDate >= @DateF AND gt.docDate < @DateT
) as CashExpenceCount


FROM    CD.Parties INNER JOIN
CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id
and CD.Parties.DocId=CD.InputLines.DocId 
INNER JOIN
vwGoodsDetailed ON CD.Parties.GoodsId = vwGoodsDetailed.Id inner join CA.Documents dd 
on dd.Id = CD.Parties.DocId left outer join CO.Departments 
on dd.DepartmentId = CO.Departments.Id
INNER JOIN @tab as t
ON CD.Parties.Id = t.pId
where dd.DocDate < @DateT" + Environment.NewLine + DepList + Environment.NewLine +
"order by 3";
            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    data.Clear();
                    data.AcceptChanges();
                    SqlCommand command = new SqlCommand(QueryString1, connection);
                    //SqlParameter depName = new SqlParameter("@DepartName", SqlDbType.VarChar, 50);
                   // depName.Value = DepName;
                   // command.Parameters.Add(depName);
                    SqlParameter dateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    dateF.Value = DateF;
                    command.Parameters.Add(dateF);
                    SqlParameter dateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    dateT.Value = DateT;
                    command.Parameters.Add(dateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            { }
            catch (Exception ee)
            { }
        }
    }
}
